package util;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import server.TodoItem;
import server.Server;

public class Database {
    private Statement stmt;
    private String url = "jdbc:sqlite:todolist.sqlite";
    private ResultSet resultSet;
    private String query="";
    private Connection conn = null;
    
    private static Database instance = new Database();
    
    private Database(){
        try
           {
           Class.forName ("org.sqlite.JDBC").newInstance ();
           conn = DriverManager.getConnection (url, "", "");
               
           }catch (Exception e){
               System.err.println ("Cannot connect to database server");
           }
    }
    
    public static TodoItem getTodoItem(int idTodoItem){
        TodoItem result=null;
        Database database = Database.instance;
        try {
            database.query = "select * from todolist where id = "+idTodoItem;
            database.stmt = (Statement) database.conn.createStatement();
            database.resultSet = database.stmt.executeQuery(database.query);
            int id;
            String content;
            int version;
            String lastUpdate;
            String dateLine;
            int priority;
            while(database.resultSet.next()){
                id = database.resultSet.getInt(1);
                version= database.resultSet.getInt(2);
                lastUpdate = database.resultSet.getString(3);
                priority = database.resultSet.getInt(4);
                content = database.resultSet.getString(5);
                dateLine = database.resultSet.getString(6);
                result = new TodoItem(id, version, content, lastUpdate, priority, dateLine);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    public static ArrayList<TodoItem> getList(){
        ArrayList<TodoItem> result = new ArrayList<TodoItem>();
        Database database = Database.instance;
        try {
            database.query = "select * from todolist";
            database.stmt = (Statement) database.conn.createStatement();
            database.resultSet = database.stmt.executeQuery(database.query);
            int id;
            TodoItem todo;
            String content;
            int version;
            String lastUpdate;
            String dateLine;
            int priority;
            while(database.resultSet.next()){
                id = database.resultSet.getInt(1);
                version= database.resultSet.getInt(2);
                lastUpdate = database.resultSet.getString(3);
                priority = database.resultSet.getInt(4);
                content = database.resultSet.getString(5);
                dateLine = database.resultSet.getString(6);
                todo = new TodoItem(id, version, content, lastUpdate, priority, dateLine);
                result.add(todo);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    public static void insertTodo(TodoItem todo){
        Database database = Database.instance;
        try {          
            database.query = "Insert into todolist (version, last_update, priority, content, dateline) values ("
                    +todo.getVersion()+",\""+todo.getLasUpdate()+"\","+todo.getPriority()+",\""+todo.getContent()+"\",\""+todo.getDeadline()+"\")";
            database.stmt = (Statement) database.conn.createStatement();
            database.stmt.executeUpdate(database.query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public static void deleteTodo(int id){
        Database database = Database.instance;
        try {          
            database.query = "delete from todolist where id = "+id;
            database.stmt = (Statement) database.conn.createStatement();
            database.stmt.executeUpdate(database.query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public static void updateTodo(TodoItem todo, int id){
        Database database = Database.instance;
        try {
//            insert into todolist values  (1,1,"2013-01-01",1,"dman","2013-02-02");
//            update todolist set version = 4 where id=2
            
            database.query = "update todolist set "
                    + "version = "+todo.getVersion()+","
                    + "last_update = \""+todo.getLasUpdate()+"\","
                    + "priority = "+todo.getPriority()+","
                    + "content = \""+todo.getContent()+"\","
                    + "dateline = \""+todo.getDeadline()+"\""
                    + "where id = "+id;
            database.stmt = (Statement) database.conn.createStatement();
//            database.resultSet = database.stmt.executeQuery(database.query);
            database.stmt.executeUpdate(database.query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public static void main(String[] args){
        Database i = instance;
        TodoItem  j = Database.getTodoItem(1);
        j.setVersion(10);
        j.setContent("alors");
        System.out.println("lastUpdate= "+ j.getLasUpdate());
        ArrayList<TodoItem> todoList = Database.getList();
        for(TodoItem todo : todoList){
            System.out.println("id = "+todo.getId());
        }
        Database.updateTodo(j, 3);
//        Database.insertTodo(j);
//        Date d = j.getDeadline();
//        System.out.println("date="+ d.getYear());
//        System.out.println("ver " + j.getVersion());
    }
}